CREATE TABLE ObjectCache (
	Context nvarchar(128) NOT NULL,
	[Key] nvarchar(128) NOT NULL,
	Item image NOT NULL,
	AbsoluteExpiration datetime NOT NULL,
	SlidingExpiration int NOT NULL
)
GO

ALTER TABLE ObjectCache ADD CONSTRAINT UC_ObjectCache UNIQUE (Context, [Key])
GO

CREATE CLUSTERED INDEX IDX_ObjectCache ON ObjectCache (Context)
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE ObjectCacheInsert (
	@Context nvarchar(128),
	@Key nvarchar(128),
	@Item image,
	@AbsoluteExpiration datetime,
	@SlidingExpiration int
)

AS

INSERT INTO ObjectCache VALUES (
	@Context,
	@Key,
	@Item,
	@AbsoluteExpiration,
	@SlidingExpiration
)
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE ObjectCacheUpdate (
	@Context nvarchar(128),
	@Key nvarchar(128),
	@Item image
)

AS

DECLARE @SlidingExpiration int
DECLARE @AbsoluteExpiration datetime

SET @AbsoluteExpiration = (SELECT AbsoluteExpiration FROM ObjectCache WHERE Context = @Context AND [Key] = @Key)
SET @SlidingExpiration = (SELECT SlidingExpiration FROM ObjectCache WHERE Context = @Context AND [Key] = @Key)

IF (@SlidingExpiration <> 0) BEGIN
	SET @AbsoluteExpiration = DATEADD(second, @SlidingExpiration, @AbsoluteExpiration)
END

UPDATE ObjectCache
SET
	Item = @Item,
	AbsoluteExpiration = @AbsoluteExpiration
WHERE
	Context = @Context
	AND [Key] = @Key
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE ObjectCacheDelete (
	@Context nvarchar(128),
	@Key nvarchar(128)
)

AS

DELETE FROM ObjectCache
WHERE
	Context = @Context
	AND [Key] = @Key
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE ObjectCacheDeleteAll

AS

DELETE FROM ObjectCache
GO


/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE ObjectCachePurge (
	@Context nvarchar(128)
)

AS

DELETE FROM ObjectCache
WHERE
	Context = @Context
	AND DATEDIFF(second, AbsoluteExpiration, GETDATE()) <= 0
GO
/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE ObjectCacheSelect (
	@Context nvarchar(128),
	@Key nvarchar(128)
)

AS

SELECT
	Context,
	[Key],
	Item,
	AbsoluteExpiration,
	SlidingExpiration
FROM
	ObjectCache
WHERE
	Context = @Context
	AND [Key] = @Key
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE dbo.ObjectCacheSelectAll (
	@Context nvarchar(128)
)

AS

SELECT
	Context,
	[Key],
	Item,
	AbsoluteExpiration,
	SlidingExpiration
FROM
	ObjectCache
WHERE
	Context = @Context
GO
